package com.yang.dao;

import com.yang.pojo.Menu1;
import com.yang.pojo.Menu2;
import com.yang.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 根据id查询一级菜单
 */

public class MenuDao {
    //根据管理员id查询对应的一级菜单
    public ArrayList<Menu1> selectFirstLevelMenuByMgrId(String mgrId) throws SQLException {
        ArrayList<Menu1> list = new ArrayList<>();
        String sql = "SELECT c.menu_id menuId,menu_code menuCode,menu_name " +
                "menuName,menu_order menuOrder, menu_level menuLevel,menu_icon menuIcon " +
                "FROM tb_mgr_role a INNER JOIN tb_role_menu b INNER JOIN tb_menus c " +
                "ON a.role_id = b.role_id AND b.menu_id = c.menu_id " +
                "WHERE a.mgr_id=? AND c.menu_level=1 order by c.menu_order";

        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        list = (ArrayList<Menu1>) queryRunner.query(sql,new BeanListHandler<Menu1>(Menu1.class),mgrId);

        return list;
    }

    //根据管理员id及一级菜单的parentMenuCode查询每个一级菜单下的二级菜单
    public ArrayList<Menu2> selectSecondLevelMenuByMgrIdAndParentCode(String mgrId,String parentMenuCode) throws SQLException {
        ArrayList<Menu2> list2 = new ArrayList<>();
        String sql = "SELECT c.menu_id menuId,menu_code menuCode,menu_name menuName,menu_order menuOrder,menu_level menuLevel,parent_menu_code parentMenuCode,menu_url menuUrl,menu_state menuState " +
                "FROM tb_mgr_role a INNER JOIN tb_role_menu b INNER JOIN tb_menus c " +
                "ON a.role_id = b.role_id AND b.menu_id = c.menu_id " +
                "WHERE a.mgr_id=? AND c.menu_level=2 AND " +
                "c.parent_menu_code=? order by c.menu_order";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        list2 = (ArrayList<Menu2>) queryRunner.query(sql, new BeanListHandler<Menu2>(Menu2.class), mgrId, parentMenuCode);

        return list2;
    }

    //查询所有的一级菜单
    public ArrayList<Menu1> selectMenu1() throws SQLException {
        ArrayList<Menu1> menu1List = new ArrayList<>();
        String sql = "SELECT menu_id menuId,menu_code menuCode,menu_name " +
                "menuName,menu_order menuOrder, menu_level menuLevel,menu_icon menuIcon FROM tb_menus WHERE menu_level = 1 order by menu_order";

        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        menu1List = (ArrayList<Menu1>) queryRunner.query(sql, new BeanListHandler<Menu1>(Menu1.class));

        return menu1List;
    }

    //查询所有的二级菜单
    public ArrayList<Menu2> selectMenu2() throws SQLException {
        ArrayList<Menu2> menu2List = new ArrayList<>();
        String sql = "SELECT menu_id menuId,menu_code menuCode,menu_name menuName,menu_order menuOrder, menu_level menuLevel,parent_menu_code parentMenuCode,menu_url menuUrl,menu_state menuState " +
                "FROM tb_menus WHERE menu_level = 2 order by menu_order";

        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        menu2List = (ArrayList<Menu2>) queryRunner.query(sql, new BeanListHandler<Menu2>(Menu2.class));

        return menu2List;
    }

    //查询一级菜单下的所有二级菜单
    public ArrayList<Menu2> selectMenu2ByMenu1(String parentMenuCode) throws SQLException {
        ArrayList<Menu2> menu2List = new ArrayList<>();
        String sql = "SELECT menu_id menuId,menu_code menuCode,menu_name menuName,menu_order menuOrder, menu_level menuLevel,parent_menu_code parentMenuCode,menu_url menuUrl,menu_state menuState " +
                "FROM tb_menus WHERE parent_menu_code = ? ORDER BY menu_order";

        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        menu2List = (ArrayList<Menu2>) queryRunner.query(sql, new BeanListHandler<Menu2>(Menu2.class),parentMenuCode);
        return menu2List;
    }

    //根据menuCode和state修改菜单状态
    public int updateMenuState(String menuCode,int state) throws SQLException {
        int i=0;
        String sql = "update tb_menus set menu_state=? where menu_code=?";
        Object[] params = {state,menuCode};
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql, params);
        return i;
    }
}
